package re.kitri.common.member;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


import re.kitri.common.DBConnect.DBConnManager;

public class MemberDAO {
	public int Join(MemberDTO dto){
		Connection conn = null;
		PreparedStatement pstmt = null;
		DBConnManager db = new DBConnManager();
		
		int jRes = 0;
		
		String sql = "insert into member values(member_seq.nextval, ?, ?, ?, ?, ?, ?, ?, ?)";
		
		try {
			conn = db.connDB();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, dto.getName());
			pstmt.setString(2, dto.getId());
			pstmt.setString(3, dto.getPw());
			pstmt.setString(4, dto.getAddress());
			pstmt.setString(5, dto.getTel());
			pstmt.setString(6, dto.getEmail());
			pstmt.setLong(7, dto.getAccount());
			pstmt.setString(8, dto.getBank());
			
			jRes = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			db.closeDB(conn, pstmt);
		}
		
		return jRes;
	}
	
	public String jungbok(MemberDTO dto){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DBConnManager db = new DBConnManager();
		String msg = "";
		
		
		String sql = "select member_id from member";
		try {
			conn = db.connDB();
			pstmt = conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			
			while(rs.next()){
				if(rs.getString("member_id").equals(dto.getId())){
					msg = "중복";
					break;
				}else if(dto.getId().equals("") || dto.getId() == null){
					msg = "사용불가";
				}
				else
					msg = "사용가능";
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			db.closeDB(conn, pstmt, rs);
		}
		return msg;
	}
	
	public ArrayList custom(MemberDTO dto){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList arr = new ArrayList();
		DBConnManager db = new DBConnManager();
		
		String sql = "select member_seq, member_id, member_name, member_pw, member_address, member_tel, " +
				"member_email, member_account, member_bank from member where member_id = ?";
		
		try {
			conn = db.connDB();
			pstmt = conn.prepareStatement(sql);
			System.out.println("custom : " + dto.getId());
			pstmt.setString(1, dto.getId());
			rs = pstmt.executeQuery();
			while(rs.next()){
				dto = new MemberDTO();
				dto.setSeq(rs.getInt("member_seq"));
				dto.setId(rs.getString("member_id"));
				dto.setName(rs.getString("member_name"));
				dto.setPw(rs.getString("member_pw"));
				dto.setAddress(rs.getString("member_address"));
				dto.setTel(rs.getString("member_tel"));
				dto.setEmail(rs.getString("member_email"));
				dto.setAccount(rs.getLong("member_account"));
				dto.setBank(rs.getString("member_bank"));
				arr.add(dto);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			db.closeDB(conn, pstmt, rs);
		}
		
		return arr;
	}
	
	public ArrayList customList(){
		ArrayList list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DBConnManager db = new DBConnManager();
		
		String sql = "select member_seq, member_id, member_name, member_pw, member_address, member_tel, " +
				"member_email, member_account, member_bank from member";
		
		try {
			conn = db.connDB();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				MemberDTO dto = new MemberDTO();
				dto.setSeq(rs.getInt("member_seq"));
				dto.setId(rs.getString("member_id"));
				dto.setName(rs.getString("member_name"));
				dto.setPw(rs.getString("member_pw"));
				dto.setAddress(rs.getString("member_address"));
				dto.setTel(rs.getString("member_tel"));
				dto.setEmail(rs.getString("member_email"));
				dto.setAccount(rs.getLong("member_account"));
				dto.setBank(rs.getString("member_bank"));
				
				list.add(dto);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			db.closeDB(conn, pstmt, rs);
		}
		
		return list;
	}
	
	public int customUpdate(MemberDTO dto){
		int uRes = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		DBConnManager db = new DBConnManager();
		
		String sql = "update member set member_pw = ? , member_address = ? , member_tel = ? , " +
				"member_email = ? , member_account = ? , member_bank=? where member_id = ?";
		
		try {
			conn = db.connDB();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, dto.getPw());
			pstmt.setString(2, dto.getAddress());
			pstmt.setString(3, dto.getTel());
			pstmt.setString(4, dto.getEmail());
			pstmt.setLong(5, dto.getAccount());
			pstmt.setString(6, dto.getBank());
			pstmt.setString(7, dto.getId());
			
			uRes = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			db.closeDB(conn, pstmt);
		}
		
		return uRes;
	}
	
	public MemberDTO login(MemberDTO dto){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DBConnManager db = new DBConnManager();
		
		String sql = "select member_id, member_pw , member_name, member_seq from member where 1=1 and member_id = ? and member_pw = ?";
		try {
			conn = db.connDB();
			pstmt = conn.prepareStatement(sql);
			
			int idx = 0;
			pstmt.setString(++idx, dto.getId());
			pstmt.setString(++idx, dto.getPw());
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				dto.setPw(rs.getString("member_pw"));
				dto.setName(rs.getString("member_name"));
				dto.setSeq(rs.getInt("member_seq"));
				dto.setId(rs.getString("member_id"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			db.closeDB(conn, pstmt, rs);
		}
		
		return dto;
		
	}

	
	public ArrayList searchCode(MemberDTO dto){
		ArrayList list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DBConnManager db = new DBConnManager();
		
		String sql = "select zipcode, sido, gugun, dong, ri, bunji from zipcode where dong like ?";
		try {
			conn = db.connDB();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%" + dto.getDong() + "%");
			rs = pstmt.executeQuery();
			while(rs.next()){
				dto =  new MemberDTO();
				
				{
					dto.setZipcode(rs.getString("zipcode"));
					dto.setSido(rs.getString("sido"));
					dto.setGugun(rs.getString("gugun"));
					dto.setDong(rs.getString("dong"));
					dto.setRi(rs.getString("ri"));
					dto.setBunji(rs.getString("bunji"));
				}
				list.add(dto);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}

	public int memDelete(MemberDTO dto){
		int dRes = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		DBConnManager db = new DBConnManager();
		
		String sql = "delete from member where member_id=?";
		
		try {
			conn=db.connDB();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, dto.getId());
			System.out.println("������? ���̵�: " + dto.getId());
			dRes = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			db.closeDB(conn, pstmt);
		}
		return dRes;
	}
	
	public int memTemp(){
		return 0;
	}
	
	public MemberDTO findId(MemberDTO dto){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DBConnManager db = new DBConnManager();
		
		String sql = "select member_id from member where member_name = ? and member_email = ?";
		
		try {
			conn = db.connDB();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, dto.getName());
			pstmt.setString(2, dto.getEmail());
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				dto.setId(rs.getString("member_id"));
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		return dto;
	}
	
	public MemberDTO findPw(MemberDTO dto){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DBConnManager db = new DBConnManager();
		
		String sql = "select member_pw from member where member_id = ? and member_account = ?";
		
		try {
			conn = db.connDB();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, dto.getId());
			pstmt.setLong(2, dto.getAccount());
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				dto.setPw(rs.getString("member_pw"));
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		return dto;
	}
}
